﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using System.ServiceModel.Activation;
using SplendidCRM.WCF.Data;
using System.Data;
using System.Globalization;
using System.Data.Common;
using System.Diagnostics;
using System.Web;
using System.IO;
using System.Threading;
using System.Data.SqlClient;

namespace SplendidCRM.WCF
{
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Required)]
    public class Splendid : ISplendid
    {
        private SplendidDataContext _data = new SplendidDataContext();

        //It is pointless rewriting/copying the login code, wrap soap class
        //Eventually, if needed this part can be rewritten for performance reasons.
        private soap _soap = new soap();

        public Splendid()
        {
            //Start timer if not created
            if (HttpContext.Current.Application["WcfTransactionsTimer"] == null)
            {
                Timer transactionTimer = new Timer(transactionTimer_Elapsed, HttpContext.Current.Application, 1000 * 30, 1000 * 30);
                HttpContext.Current.Application["WcfTransactionsTimer"] = transactionTimer;
            }
        }

        void transactionTimer_Elapsed(object arg)
        {
            HttpApplicationState state = arg as HttpApplicationState;

            //Because we are rolling back a transaction, we need to start again because the Enumerator will no longer be valid.
            Guid toRollback = Guid.Empty;

            do
            {
                //Reset toRollback
                toRollback = Guid.Empty;

                //We cannot lock a null object
                if (state["WcfTransactions"] != null)
                {
                    //lock to make sure we dont get a exception if a web service call changes the collection
                    lock (state["WcfTransactions"])
                    {
                        foreach (KeyValuePair<Guid, OpenTransaction> item in (Dictionary<Guid, OpenTransaction>)state["WcfTransactions"])
                        {
                            //Timeout of 2 mins
                            if (item.Value.LastAccess.Subtract(DateTime.Now) > new TimeSpan(0, 2, 0))
                            {
                                toRollback = item.Key;
                            }
                        }
                    }
                }

                if (toRollback != Guid.Empty)
                    RollbackTransaction(toRollback);

            } while (toRollback != Guid.Empty);
        }

        public List<Module> GetAvailableModules(AvilableModuleMode mode)
        {
            L10N L10n = new L10N(SplendidDefaults.Culture());

            //We only want modules that are enabled, are not admin views and the current user is allowed to see
            //Admin views have no place in a client tool, the Website should be used for all administration
            List<Module> modules = _data.VwMODULES_USERS_Cross.Where(m => m.USER_ID ==
                Security.USER_ID && m.MODULE_ENABLED == true && m.IS_ADMIN == false)
                .OrderBy(m => m.MODULE_NAME).ToList();

            if (mode == AvilableModuleMode.Searchable)
            {
                //If the module has a listview the module can be searched.
                //Also check if it has more than 0 columns
                List<string> modulesWithListView = _data.ExecuteQuery<string>(
                    "SELECT MODULE_NAME FROM vwGRIDVIEWS gv "+
                    "WHERE ((SELECT COUNT(*) FROM vwGRIDVIEWS_COLUMNS gvc WHERE gvc.GRID_NAME = gv.NAME) > 0) "+
                    "AND NAME LIKE '%ListView'").ToList();

                for (int i = 0; i < modules.Count; i++)
                {
                    //iFrames is not searchable nor are modules with no gridview columns
                    if (modules[i].MODULE_NAME == "iFrames" || !modulesWithListView.Contains(modules[i].MODULE_NAME))
                        modules.RemoveAt(i--);
                }
            }
            else if (mode == AvilableModuleMode.CanAttachEmail)
            {

            }
            else if (mode == AvilableModuleMode.CanAttachNote)
            {

            }

            modules.ForEach((Action<Module>)delegate(Module m)
                {
                    m.DISPLAY_NAME = L10n.Term(m.DISPLAY_NAME);
                });

            return modules;
        }

        public LoginResults Login(string applicationName)
        {
            //I am pretty much using this as a test method now.
            soap.set_entry_result result = _soap.login(new soap.user_auth(), applicationName);

            return new LoginResults()
            {
                SessionId = new Guid(result.id),
                Error = (result.error.name == "No Error" ? null : new Error()
                {
                    Description = result.error.description,
                    Name = result.error.name,
                    Number = Convert.ToInt32(result.error.number)
                })
            };
        }

        public List<GridViewColumn> GetGridViewColumns(Module module)
        {
            List<GridViewColumn> columns = _data.VwGRIDVIEWS_COLUMNS.Where(c => c.GRID_NAME == module.MODULE_NAME + ".ListView" &&
                c.DEFAULT_VIEW == false && c.DELETED == false).OrderBy(c => c.COLUMN_INDEX).ToList();

            L10N L10n = new L10N(SplendidDefaults.Culture());

            foreach (GridViewColumn column in columns)
                column.HEADER_TEXT = L10n.Term(column.HEADER_TEXT);

            return columns;
        }

        public SearchResults Search(string searchTerm, Module module, int offset, int maxResults)
        {
            //Build search
            List<SearchField> fields = new List<SearchField>();
            foreach (GridViewColumn column in GetGridViewColumns(module))
	        {
                //Try to filter any date fields, cant use LIKE on date fields
                if (column.DATA_FIELD.IndexOf("Date", StringComparison.OrdinalIgnoreCase) == -1)
                    fields.Add(new SearchField() { Field = column.DATA_FIELD, Value = searchTerm, Operator = Operator.Like });
            }

            return SearchFields(module, offset, maxResults, fields.ToArray());
        }

        /// <summary>
        /// Copied from soap.asmx.cs
        /// Gets the table name of the module
        /// </summary>
        /// <param name="sMODULE_NAME"></param>
        /// <returns></returns>
        private string VerifyModuleName(string sMODULE_NAME)
        {
            vwMODULES module = _data.VwMODULES.SingleOrDefault(m => m.MODULE_NAME == sMODULE_NAME);

            if (module == null)
                throw (new Exception("This module is not available on this server"));
            else
                return module.TABLE_NAME;
        }

        public List<EditViewField> GetSearchFields(Module module, bool advancedSearch)
        {
            return _data.VwEDITVIEWS_FIELDS.Where(f => f.EDIT_NAME == module.MODULE_NAME + (advancedSearch ? ".SearchAdvanced" : ".SearchBasic") &&
                f.DEFAULT_VIEW == false).ToList();
        }

        private SearchResults SearchFields(Module module, string viewToSearch, int offset, int maxResults, params SearchField[] fieldToSearch)
        {
            if (offset < 0)
                throw (new Exception("offset must be a non-negative number"));
            if (maxResults <= 0)
                throw (new Exception("max_results must be a postive number"));

            if (viewToSearch == null)
                viewToSearch = _data.VwGRIDVIEWS.Single(g => g.NAME == module.MODULE_NAME + ".ListView").VIEW_NAME;

            int nACLACCESS = Security.GetUserAccess(module.MODULE_NAME, "list");
            if (nACLACCESS < 0)
            {
                L10N L10n = new L10N(SplendidDefaults.Culture());
                throw (new Exception(L10n.Term("ACL.LBL_INSUFFICIENT_ACCESS")));
            }

            SearchResults results = new SearchResults();

            DbProviderFactory dbf = DbProviderFactories.GetFactory();
            using (IDbConnection con = dbf.CreateConnection())
            {
                con.Open();
                StringBuilder sqlBuilder = new StringBuilder();

                sqlBuilder.AppendFormat("SELECT MODULE = '{0}', * ", module.MODULE_NAME);

                results.Columns = GetGridViewColumns(module);

                if (results.Columns.Count == 0)
                    throw new Exception("Module has no columns defined for Search view");

                results.Columns.Insert(0, new GridViewColumn() { DATA_FIELD = "ID", COLUMN_TYPE = "HiddenColumn" });
            
                sqlBuilder.AppendFormat("FROM {0}\r\n", viewToSearch);
                StringBuilder where = new StringBuilder();

                bool lastFieldWasCondition = false;
                int fieldCount = 0;
                foreach (SearchField field in fieldToSearch)
                {
                    //Make sure there are no comments/spaces in the field to protect against basic sql injection
                    if (!string.IsNullOrEmpty(field.Field))
                        field.Field = field.Field.Replace("--", string.Empty).Replace(" ", string.Empty);

                    if (lastFieldWasCondition && field.Operator != Operator.And && field.Operator != Operator.Or &&
                        field.Operator != Operator.CloseBracker)
                        where.AppendLine(" OR ");

                    switch (field.Operator)
                    {
                        case Operator.Equal:
                            where.AppendLine(field.Field + " = @" + (field.Field + fieldCount));
                            lastFieldWasCondition = true;
                            break;
                        case Operator.NotEqual:
                            where.AppendLine(field.Field + " <> @" + (field.Field + fieldCount));
                            lastFieldWasCondition = true;
                            break;
                        case Operator.Like:
                            where.AppendLine(field.Field + " LIKE '%' + @" + (field.Field + fieldCount) + " + '%'");
                            lastFieldWasCondition = true;
                            break;
                        case Operator.NotLike:
                            where.AppendLine(field.Field + " NOT LIKE '%' + @" + (field.Field + fieldCount) + " + '%'");
                            lastFieldWasCondition = true;
                            break;
                        case Operator.LessThan:
                            where.AppendLine(field.Field + " < @" + (field.Field + fieldCount));
                            lastFieldWasCondition = true;
                            break;
                        case Operator.LessThanOrEqual:
                            where.AppendLine(field.Field + " <= @" + (field.Field + fieldCount));
                            lastFieldWasCondition = true;
                            break;
                        case Operator.GreaterThan:
                            where.AppendLine(field.Field + " > @" + (field.Field + fieldCount));
                            lastFieldWasCondition = true;
                            break;
                        case Operator.GreaterThanOrEqual:
                            where.AppendLine(field.Field + " >= @" + (field.Field + fieldCount));
                            lastFieldWasCondition = true;
                            break;
                        case Operator.IsNull:
                            where.AppendLine(field.Field + " IS NULL");
                            lastFieldWasCondition = true;
                            break;
                        case Operator.IsNotNull:
                            where.AppendLine(field.Field + " IS NOT NULL");
                            lastFieldWasCondition = true;
                            break;
                        case Operator.OpenBracket:
                            where.Append(" ( ");
                            lastFieldWasCondition = false;
                            break;
                        case Operator.CloseBracker:
                            where.AppendLine(" ) ");
                            lastFieldWasCondition = true;
                            break;
                        case Operator.Or:
                            where.Append(" OR ");
                            lastFieldWasCondition = false;
                            break;
                        case Operator.And:
                            where.Append(" AND ");
                            lastFieldWasCondition = false;
                            break;
                    }

                    fieldCount++;
                }

                using (IDbCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = sqlBuilder.ToString();

                    Security.Filter(cmd, module.MODULE_NAME, "edit");
                    if (where.Length > 0)
                        cmd.CommandText += "AND (" + where.ToString() + ") ";

                    //Add parameters
                    fieldCount = 0;
                    foreach (SearchField field in fieldToSearch)
                    {
                        if (!string.IsNullOrEmpty(field.Field) && !string.IsNullOrEmpty(field.Value))
                        {
                            Sql.AddParameter(cmd, "@" + (field.Field + fieldCount), field.Value);
                        }

                        fieldCount++;
                    }
                    try
                    {
                        IDataReader reader = cmd.ExecuteReader();

                        int recordsSkipped = 0;
                        int recordsRead = 0;

                        //No need to read offset records, just move internal pointer through until we get to the record we need
                        while (recordsSkipped < offset)
                        {
                            if (!reader.NextResult())
                                break;
                        }

                        //Read values and build the results
                        while (reader.Read() && recordsRead < maxResults)
                        {
                            Dictionary<string, string> fields = new Dictionary<string, string>();

                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                fields.Add(reader.GetName(i), reader.GetValue(i).ToString());
                            }
                            results.Results.Add(fields);

                            recordsRead++;
                        }
                    }
                    catch (Exception ex)
                    {
                        SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
                        throw (new Exception("SOAP: Failed Search", ex));
                    }
                }
            }
            return results;
        }

        public SearchResults SearchFields(Module module, int offset, int maxResults, params SearchField[] fieldToSearch)
        {
            //string sTABLE_NAME = VerifyModuleName(module.MODULE_NAME);

            //// 26/08/08 Jake.  Project module has views named PROJECTS, but table is only PROJECT, perform replace
            //// 9/09/08 Jake    Same with Project Tasks..
            //if (sTABLE_NAME == "PROJECT")
            //    sTABLE_NAME = "PROJECTS";
            //if (sTABLE_NAME == "PROJECT_TASK")
            //    sTABLE_NAME = "PROJECT_TASKS";

            return SearchFields(module, null, offset, maxResults, fieldToSearch);
        }

        public List<Terminology> GetListValues(Module module, string listName)
        {
            //Try get the list using the module name
            List<Terminology> values = _data.VwTERMINOLOGY_List
                .Where(t => (t.LIST_NAME == listName && t.MODULE_NAME == (module == null ? null : module.MODULE_NAME)))
                .OrderBy(t => t.DISPLAY_NAME)
                .ToList();

            //If we cant get a list by specifying the module name, try null
            if (values.Count == 0 && module != null)
                values = _data.VwTERMINOLOGY_List
                    .Where(t => (t.LIST_NAME == listName && t.MODULE_NAME == null))
                    .OrderBy(t => t.DISPLAY_NAME)
                    .ToList();

            return values;
        }

        public UpdateResults UpdateEntry(Guid transactionId, Module module, Dictionary<string, string> values)
        {
            string sTABLE_NAME = VerifyModuleName(module.MODULE_NAME);
            int nACLACCESS = Security.GetUserAccess(module.MODULE_NAME, "edit");
            if (nACLACCESS < 0)
            {
                L10N L10n = new L10N(SplendidDefaults.Culture());
                throw (new Exception(L10n.Term("ACL.LBL_INSUFFICIENT_ACCESS")));
            }

            string storedProcName = sTABLE_NAME;

            // 26/08/08 Jake.  Project module has views named PROJECTS, but table is only PROJECT, perform replace
            // 9/09/08 Jake    Same with Project Tasks..
            if (storedProcName == "PROJECT")
                storedProcName = "PROJECTS";
            if (storedProcName == "PROJECT_TASK")
                storedProcName = "PROJECT_TASKS";

            UpdateResults results = new UpdateResults();

            OpenTransaction trn = GetTransaction(transactionId);
            // 02/21/2006 Paul.  Delete operations come in as set_entry with deleted = 1. 
            if (values.ContainsKey("DELETED") && Convert.ToBoolean(values["DELETED"]))
            {
                DeleteEntry(transactionId, module, new Guid(values["ID"]));
            }
            else
            {
                try
                {
                    IDbCommand cmdUpdate = SqlProcs.Factory(trn.Connection, "sp" + storedProcName + "_Update");
                    cmdUpdate.Transaction = trn.Transaction;
                    IDbDataParameter parID = Sql.FindParameter(cmdUpdate, "@ID");
                    // 10/10/2006 Paul.  Use IDbDataParameter to be consistent. 
                    foreach (IDbDataParameter par in cmdUpdate.Parameters)
                    {
                        par.Value = DBNull.Value;
                    }
                    // 08/31/2006 Paul.  We need to initialize the values of any fields not provided. 
                    // The stored procedure always updates all fields, so we need to make sure not to clear fields that are not provided. 
                    // This problem was first noticed when the Outlook Plug-in kept clearing the ASSIGNED_USER_ID field. 
                    if (values.ContainsKey("ID"))
                    {
                        // 08/31/2006 Paul.  If the ID is not found, then this must be a new 
                        InitializeParameters(trn, sTABLE_NAME, new Guid(values["ID"]), cmdUpdate);
                    }

                    //We need to convert PROJECT_ID to PARENT_ID because this module seems to love things
                    //that do not follow the convensions.
                    //if (sTABLE_NAME == "PROJECT_TASKS" && cmdUpdate.Parameters.Contains("PROJECT_ID"))
                    //{
                    //    Sql.AddParameter(cmdUpdate, "PARENT_ID", (Guid)((IDbDataParameter)cmdUpdate.Parameters["PROJECT_ID"]).Value);
                    //}

                    Sql.SetParameter(cmdUpdate, "@MODIFIED_USER_ID", GetUserId().ToString());

                    foreach (KeyValuePair<string, string> value in values)
                    {
                        if (value.Value != null)
                            Sql.SetParameter(cmdUpdate, "@" + value.Key, value.Value);
                    }
                    cmdUpdate.ExecuteNonQuery();

                    if (transactionId == Guid.Empty)
                        CommitAndDispose(trn);

                    if (parID != null)
                    {
                        results.Id = new Guid(parID.Value.ToString());
                    }
                }
                catch (Exception ex)
                {
                    if (transactionId == Guid.Empty)
                        RollbackAndDispose(trn);
                    else
                        RollbackTransaction(transactionId);
                    SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
                    throw (new Exception(ex.Message));
                }
            }
            return results;
        }

        /// <summary>
        /// Initializes the parameters. Copied from soap.asmx until it can be written.
        /// </summary>
        /// <param name="con">The con.</param>
        /// <param name="sTABLE_NAME">The s TABL e_ NAME.</param>
        /// <param name="gID">The g ID.</param>
        /// <param name="cmdUpdate">The CMD update.</param>
        private void InitializeParameters(OpenTransaction tran, string sTABLE_NAME, Guid gID, IDbCommand cmdUpdate)
        {
            String sSQL = String.Empty;
            sSQL = "select *" + ControlChars.CrLf
                 + "  from " + sTABLE_NAME + ControlChars.CrLf
                 + " where ID = @ID" + ControlChars.CrLf;
            using (IDbCommand cmd = tran.Connection.CreateCommand())
            {
                cmd.Transaction = tran.Transaction;
                cmd.CommandText = sSQL;
                Sql.AddParameter(cmd, "@ID", gID);
                try
                {
                    DbProviderFactory dbf = DbProviderFactories.GetFactory();
                    using (DbDataAdapter da = dbf.CreateDataAdapter())
                    {
                        ((IDbDataAdapter)da).SelectCommand = cmd;
                        using (DataTable dt = new DataTable())
                        {
                            da.Fill(dt);
                            if (dt.Rows.Count > 0)
                            {
                                DataRow row = dt.Rows[0];
                                foreach (DataColumn col in dt.Columns)
                                {
                                    IDbDataParameter par = Sql.FindParameter(cmdUpdate, col.ColumnName);
                                    if (par != null)
                                    {
                                        par.Value = row[col.ColumnName];
                                    }
                                }
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
                    throw (new Exception(ex.Message));
                }
            }
        }

        public UpdateResults SetNoteAttachment(Guid transactionId, NoteAttachment attachment)
        {
            if (attachment.NOTE_ID == null || attachment.NOTE_ID == Guid.Empty)
                throw new Exception("NOTE_ID must be set for attachment");

            attachment.FILENAME = Path.GetFileName(attachment.FILENAME);

            if (string.IsNullOrEmpty(attachment.FILE_MIME_TYPE))
                attachment.FILE_MIME_TYPE = "application/octet-stream";

            int nACLACCESS = Security.GetUserAccess("Notes", "edit");
            if (nACLACCESS < 0)
            {
                L10N L10n = new L10N(SplendidDefaults.Culture());
                throw (new Exception(L10n.Term("ACL.LBL_INSUFFICIENT_ACCESS")));
            }

            UpdateResults result = new UpdateResults();

            using (MemoryStream stm = new System.IO.MemoryStream(attachment.ATTACHMENT.ToArray()))
            {
                OpenTransaction trn = GetTransaction(transactionId);

                try
                {
                    Guid gAttachmentID = Guid.Empty;
                    SqlProcs.spNOTE_ATTACHMENTS_Insert(ref gAttachmentID, attachment.NOTE_ID.Value, attachment.DESCRIPTION,
                        attachment.FILENAME, attachment.FILE_EXT, attachment.FILE_MIME_TYPE, trn.Transaction);
                    SplendidCRM.Notes.EditView.LoadFile(gAttachmentID, stm, trn.Transaction);

                    //Set Id in results
                    attachment.ID = gAttachmentID;
                    result.Id = gAttachmentID;

                    if (transactionId == Guid.Empty)
                        CommitAndDispose(trn);
                }
                catch (Exception ex)
                {
                    if (transactionId == Guid.Empty)
                        RollbackAndDispose(trn);
                    else
                        RollbackTransaction(transactionId);
                    SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
                    throw (new Exception(ex.Message));
                }
            }

            return result;
        }

        public Module GetModuleInfo(string moduleName)
        {
            Guid userId = GetUserId();

            if (userId == Guid.Empty)
                return _data.VwMODULES_USERS_Cross.Where(m => m.MODULE_NAME == moduleName).FirstOrDefault();
            else
                return _data.VwMODULES_USERS_Cross.Where(m => m.USER_ID ==
                GetUserId() && m.MODULE_NAME == moduleName).SingleOrDefault();
        }

        public UpdateResults AddDocumentRevision(Guid transactionId, DocumentRevision revision)
        {
            if (revision.DOCUMENT_ID == null)
                throw new Exception("Revision doesnt have a associated document ID");

            revision.FILENAME = Path.GetFileName(revision.FILENAME);

            if (string.IsNullOrEmpty(revision.FILE_EXT))
                revision.FILE_EXT = Path.GetExtension(revision.FILE_EXT);

            if(string.IsNullOrEmpty(revision.FILE_MIME_TYPE))
                revision.FILE_MIME_TYPE = "application/octet-stream";

            VerifyAccess("Documents", "edit");

            UpdateResults result = new UpdateResults();

            using (MemoryStream stm = new System.IO.MemoryStream(revision.CONTENT.ToArray()))
            {
                OpenTransaction trn = GetTransaction(transactionId);
                try
                {
                    Guid gRevID = Guid.Empty;

                    SqlProcs.spDOCUMENT_REVISIONS_Insert(ref gRevID, revision.DOCUMENT_ID.Value, revision.REVISION, "Document Created", revision.FILENAME, revision.FILE_EXT,
                        revision.FILE_MIME_TYPE, trn.Transaction);

                    SplendidCRM.Documents.EditView.LoadFile(gRevID, stm, trn.Transaction);

                    result.Id = gRevID;

                    if (transactionId == Guid.Empty)
                        CommitAndDispose(trn);
                }
                catch (Exception ex)
                {
                    if (transactionId == Guid.Empty)
                        RollbackAndDispose(trn);
                    else
                        RollbackTransaction(transactionId);
                    SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
                    throw (new Exception(ex.Message));
                }
            }
            return result;
        }

        private int VerifyAccess(string module, string action)
        {
            int nACLACCESS = Security.GetUserAccess(module, action);
            if (nACLACCESS < 0)
            {
                L10N L10n = new L10N(SplendidDefaults.Culture());
                throw (new Exception(L10n.Term("ACL.LBL_INSUFFICIENT_ACCESS")));
            }

            return nACLACCESS;
        }

        public UpdateResults NewDocument(Guid transactionId, Document document, DocumentRevision revision)
        {
            revision.FILENAME = Path.GetFileName(revision.FILENAME);

            if (string.IsNullOrEmpty(revision.FILE_EXT))
                revision.FILE_EXT = Path.GetExtension(revision.FILE_EXT);

            if (string.IsNullOrEmpty(revision.FILE_MIME_TYPE))
                revision.FILE_MIME_TYPE = "application/octet-stream";

            int nACLACCESS = VerifyAccess("Documents", "edit");

            UpdateResults result = new UpdateResults();

            using (MemoryStream stm = new System.IO.MemoryStream(revision.CONTENT.ToArray()))
            {
                OpenTransaction trn = GetTransaction(transactionId);

                try
                {
                    Guid gRevID = Guid.Empty;
                    Guid documentId = Guid.Empty;

                    SqlProcs.spDOCUMENTS_Update(ref documentId,
                        document.DOCUMENT_NAME,
                        document.ACTIVE_DATE == null || document.ACTIVE_DATE == DateTime.MinValue ? DateTime.Today : document.ACTIVE_DATE.Value,
                        document.EXP_DATE == null ? DateTime.MinValue : document.EXP_DATE.Value,
                        document.CATEGORY_ID,
                        document.SUBCATEGORY_ID,
                        document.STATUS_ID,
                        document.DESCRIPTION,
                        document.MAIL_MERGE_DOCUMENT == null ? false : document.MAIL_MERGE_DOCUMENT.Value,
                        document.RELATED_DOC_ID == null ? Guid.Empty : document.RELATED_DOC_ID.Value,
                        document.RELATED_DOC_REV_ID == null ? Guid.Empty : document.RELATED_DOC_REV_ID.Value,
                        document.IS_TEMPLATE == null ? false : document.IS_TEMPLATE.Value,
                        document.TEMPLATE_TYPE,
                        document.TEAM_ID == null ? Guid.Empty : document.TEAM_ID.Value, trn.Transaction);

                    SqlProcs.spDOCUMENT_REVISIONS_Insert(ref gRevID, documentId, revision.REVISION, "Document Created", revision.FILENAME, revision.FILE_EXT,
                        revision.FILE_MIME_TYPE, trn.Transaction);

                    SplendidCRM.Documents.EditView.LoadFile(gRevID, stm, trn.Transaction);

                    result.Id = gRevID;

                    //Commit transaction if not a user initiated transaction
                    if (transactionId == Guid.Empty)
                        CommitAndDispose(trn);
                }
                catch (Exception ex)
                {
                    //Commit transaction if not a user initiated transaction
                    if (transactionId == Guid.Empty)
                        RollbackAndDispose(trn);
                    else
                        //Automatically rollback and cleanup on error
                        RollbackTransaction(transactionId);
                    SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
                    throw (new Exception(ex.Message));
                }

            }
            return result;
        }

        public void RelateEntries(Guid transactionId, Module module1, Guid entry1Id, Module module2, Guid entry2Id)
        {
            //We have to commit transaction otherwise we get a deadlock.
            if (transactionId != Guid.Empty)
                CommitAndDispose(GetTransaction(transactionId));

            Module lhsModule = module1;
            Module rhsModule = module2;
            Guid lhsId = entry1Id;
            Guid rhsId = entry2Id;

            vwRELATIONSHIPS relationship = _data.VwRELATIONSHIPS.SingleOrDefault(r =>
                r.LHS_MODULE == module1.MODULE_NAME && r.RHS_MODULE == module2.MODULE_NAME);

            //Try the opposite way
            if (relationship == null)
            {
                relationship = _data.VwRELATIONSHIPS.SingleOrDefault(r =>
                    r.LHS_MODULE == module2.MODULE_NAME && 
                    r.RHS_MODULE == module1.MODULE_NAME);
                lhsModule = module2;
                rhsModule = module1;
                lhsId = entry2Id;
                rhsId = entry1Id;
            }

            //Notes are a special case
            if (module2.MODULE_NAME == "Notes")
            {
                //Fetch note info
                Dictionary<string, string> values = GetEntryValues(module2, entry2Id);

                values["PARENT_TYPE"] = module1.MODULE_NAME;
                values["PARENT_ID"] = entry1Id.ToString();

                UpdateEntry(Guid.Empty, module2, values);
            }
            else if (relationship.RELATIONSHIP_TYPE == "one-to-many")
            {
                Dictionary<string,string> values = GetEntryValues(rhsModule, rhsId);

                values[relationship.RHS_KEY] = lhsId.ToString();
                values[relationship.RELATIONSHIP_ROLE_COLUMN] = relationship.RELATIONSHIP_ROLE_COLUMN_VALUE;

                UpdateEntry(Guid.Empty, rhsModule, values);
            }
            else if (relationship.RELATIONSHIP_TYPE == "many-to-many")
            {
                OpenTransaction trn = GetTransaction(Guid.Empty);
                try
                {
                    IDbCommand cmdUpdate = SqlProcs.Factory(trn.Connection, "sp" + relationship.JOIN_TABLE + "_Update");
                    cmdUpdate.Transaction = trn.Transaction;
                    foreach (IDbDataParameter par in cmdUpdate.Parameters)
                    {
                        par.Value = DBNull.Value;
                    }

                    Sql.SetParameter(cmdUpdate, "@MODIFIED_USER_ID", GetUserId().ToString());

                    Sql.SetParameter(cmdUpdate, "@" + relationship.JOIN_KEY_LHS, lhsId.ToString());
                    Sql.SetParameter(cmdUpdate, "@" + relationship.JOIN_KEY_RHS, rhsId.ToString());

                    cmdUpdate.ExecuteNonQuery();

                    //Commit transaction
                    CommitAndDispose(trn);
                }
                catch (Exception ex)
                {
                    //Commit transaction if not a user initiated transaction
                    RollbackAndDispose(trn);
                    SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
                    throw (new Exception(ex.Message));
                }
            }
            else
                throw new Exception("Relationship doesnt exist!");
        }

        public UpdateResults RelateNoteToEntry(Guid transactionId, Module entryModule, Guid entryId, Guid noteId)
        {
            //We have to commit transaction otherwise we get a deadlock.
            if (transactionId != Guid.Empty)
                CommitAndDispose(GetTransaction(transactionId));

            Guid gPARENT_ID = Guid.Empty;
            Guid gCONTACT_ID = Guid.Empty;
            if (String.Compare(entryModule.MODULE_NAME, "Contacts", true) == 0)
                gCONTACT_ID = Sql.ToGuid(entryId);
            else
                gPARENT_ID = Sql.ToGuid(entryId);

            int nACLACCESS = Security.GetUserAccess("Notes", "edit");
            if (nACLACCESS < 0)
            {
                L10N L10n = new L10N(SplendidDefaults.Culture());
                throw (new Exception(L10n.Term("ACL.LBL_INSUFFICIENT_ACCESS")));
            }

            UpdateResults results = new UpdateResults();

            OpenTransaction trn = GetTransaction(transactionId);

            try
            {
                string sSQL;
                sSQL = "select *           " + ControlChars.CrLf
                     + "  from vwNOTES_Edit" + ControlChars.CrLf
                     + " where ID = @ID    " + ControlChars.CrLf;
                using (IDbCommand cmd = trn.Connection.CreateCommand())
                {
                    cmd.Transaction = trn.Transaction;
                    cmd.CommandText = sSQL;
                    Sql.AddParameter(cmd, "@ID", noteId);
                    using (IDataReader rdr = cmd.ExecuteReader())
                    {
                        if (rdr.Read())
                        {
                            string sNAME = Sql.ToString(rdr["NAME"]);
                            string sDESCRIPTION = Sql.ToString(rdr["DESCRIPTION"]);
                            // 09/01/2006 Paul.  Notes do not have an ASSIGNED_USER_ID. 
                            Guid gASSIGNED_USER_ID = Guid.Empty;  // Sql.ToGuid  (rdr["ASSIGNED_USER_ID"]);

                            Guid gNoteId = noteId;

                            SqlProcs.spNOTES_Update
                                (ref noteId
                                , sNAME
                                , entryModule.MODULE_NAME
                                , gPARENT_ID
                                , gCONTACT_ID
                                , sDESCRIPTION
                                , Sql.ToGuid(rdr["TEAM_ID"]),
                                trn.Transaction
                                );
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                //Commit transaction if not a user initiated transaction
                if (transactionId == Guid.Empty)
                    RollbackAndDispose(trn);
                else
                    //Automatically rollback and cleanup on error
                    RollbackTransaction(transactionId);
                SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
                throw (new Exception(ex.Message));
            }
            return results;
        }

        public UpdateResults DeleteEntry(Guid transactionId, Module module, Guid entryId)
        {
            string sTABLE_NAME = VerifyModuleName(module.MODULE_NAME);

            OpenTransaction trn = GetTransaction(transactionId);
            try
            {
                IDbCommand cmdDelete = SqlProcs.Factory(trn.Connection, "sp" + sTABLE_NAME + "_Delete");
                cmdDelete.Transaction = trn.Transaction;

                // 10/10/2006 Paul.  Use IDbDataParameter to be consistent. 
                foreach (IDbDataParameter par in cmdDelete.Parameters)
                {
                    par.Value = DBNull.Value;
                }
                Sql.SetParameter(cmdDelete, "@MODIFIED_USER_ID", GetUserId());
                if (entryId != Guid.Empty)
                {
                    Sql.SetParameter(cmdDelete, "@ID", entryId.ToString());
                    cmdDelete.ExecuteNonQuery();
                }
                else
                {
                    return new UpdateResults()
                    {
                        Error = new Error()
                        {
                            Name = "Entry Id Missing",
                            Description = "Entry ID must be provided to delete a entry"
                        }
                    };
                }

                if (transactionId == Guid.Empty)
                    CommitAndDispose(trn);
            }
            catch (Exception ex)
            {
                //Commit transaction if not a user initiated transaction
                if (transactionId == Guid.Empty)
                    RollbackAndDispose(trn);
                else
                    //Automatically rollback and cleanup on error
                    RollbackTransaction(transactionId);
                SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
                throw (new Exception(ex.Message));
            }
            return new UpdateResults();
        }

        private Guid GetUserId()
        {
            if (ServiceSecurityContext.Current == null)
                return Guid.Empty;

            return _data.VwUSERS.Single(u => u.USER_NAME == ServiceSecurityContext.Current.WindowsIdentity.Name.Split('\\')[1]).ID;
        }

        public Dictionary<string, string> GetEntryValues(Module module, Guid id)
        {
            Dictionary<string, string> values = new Dictionary<string, string>();

            string tableName = VerifyModuleName(module.MODULE_NAME);

            if (tableName == "PROJECT_TASK")
                tableName = "PROJECT_TASKS";
            else if (tableName == "PROJECT")
                tableName = "PROJECT";

            String sSQL = String.Empty;
            sSQL = "select *" + ControlChars.CrLf
                 + "  from vw" + tableName + "_Edit" + ControlChars.CrLf
                 + " where ID = @ID" + ControlChars.CrLf;

            DbProviderFactory dbf = DbProviderFactories.GetFactory();
            using (IDbConnection con = dbf.CreateConnection())
            {
                con.Open();
                using (IDbCommand cmd = con.CreateCommand()) 
                {
                    cmd.CommandText = sSQL;
                    Sql.AddParameter(cmd, "@ID", id.ToString());
                    try
                    {
                        using (DbDataAdapter da = dbf.CreateDataAdapter())
                        {
                            ((IDbDataAdapter)da).SelectCommand = cmd;
                            using (DataTable dt = new DataTable())
                            {
                                da.Fill(dt);
                                if (dt.Rows.Count > 0)
                                {
                                    DataRow row = dt.Rows[0];
                                    foreach (DataColumn col in dt.Columns)
                                    {
                                        values.Add(col.ColumnName, row[col.ColumnName].ToString());
                                    }
                                }
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
                    }
                }
            }

            return values;
        }

        public List<EditViewField> GetEditFields(Module module)
        {
            return _data.VwEDITVIEWS_FIELDS.Where(f => f.EDIT_NAME == module.MODULE_NAME + ".EditView" &&
                 (f.FIELD_TYPE == "TextBox" || f.FIELD_TYPE == "ListBox") &&
                 f.DEFAULT_VIEW == false).ToList();
        }

        private void UpdateCustomFields(IDbTransaction trn, Module module, Guid gID, Dictionary<string, string> values)
        {

            using (IDbCommand cmd = trn.Connection.CreateCommand())
            {
                cmd.Transaction = trn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "update " + module.MODULE_NAME + "_CSTM" + ControlChars.CrLf;
                int nFieldIndex = 0;
                foreach (DataRow row in SplendidCache.FieldsMetaData_Validated(module.MODULE_NAME).Rows)
                {
                    // 01/11/2006 Paul.  Uppercase looks better. 
                    string sNAME = Sql.ToString(row["NAME"]).ToUpper();
                    string sCsType = Sql.ToString(row["CsType"]);
                    // 01/13/2007 Paul.  We need to truncate any long strings to prevent SQL error. 
                    // String or binary data would be truncated. The statement has been terminated. 
                    int nMAX_SIZE = Sql.ToInteger(row["MAX_SIZE"]);
                    // 02/10/2008 Paul.  Literals should not be updated. 
                    if (values.ContainsKey(sNAME))
                    {
                        if (nFieldIndex == 0)
                            cmd.CommandText += "   set ";
                        else
                            cmd.CommandText += "     , ";
                        // 01/10/2006 Paul.  We can't use a StringBuilder because the Sql.AddParameter function
                        // needs to be able to replace the @ with the appropriate database specific token. 
                        cmd.CommandText += sNAME + " = @" + sNAME + ControlChars.CrLf;

                        //DynamicControl ctlCustomField_File = new DynamicControl(ctlPARENT, sNAME + "_File");
                        // 04/21/2006 Paul.  If the type is Guid and it is accompanied by a File control, then assume it is an image. 
                        //if (sCsType == "Guid" && ctlCustomField.Type == "HtmlInputHidden" && ctlCustomField_File.Exists)
                        //{
                        //    LoadImage(ctlPARENT, gID, sNAME, trn);
                        //}
                        // 04/21/2006 Paul.  Even if there is no image to upload, we still need to update the field.
                        // This is so that the image can be cleared. 
                        switch (sCsType)
                        {
                            case "Guid": Sql.AddParameter(cmd, "@" + sNAME, new Guid(values[sNAME])); break;
                            case "short": Sql.AddParameter(cmd, "@" + sNAME, Convert.ToInt16(values[sNAME])); break;
                            case "Int32": Sql.AddParameter(cmd, "@" + sNAME, Convert.ToInt32(values[sNAME])); break;
                            case "Int64": Sql.AddParameter(cmd, "@" + sNAME, Convert.ToInt64(values[sNAME])); break;
                            case "float": Sql.AddParameter(cmd, "@" + sNAME, Convert.ToSingle(values[sNAME])); break;
                            case "decimal": Sql.AddParameter(cmd, "@" + sNAME, Convert.ToDecimal(values[sNAME])); break;
                            case "bool": Sql.AddParameter(cmd, "@" + sNAME, Convert.ToBoolean(values[sNAME])); break;
                            case "DateTime": Sql.AddParameter(cmd, "@" + sNAME, DateTime.Parse(values[sNAME])); break;
                            default: Sql.AddParameter(cmd, "@" + sNAME, values[sNAME].ToString(), nMAX_SIZE); break;
                        }
                        nFieldIndex++;
                    }
                }
                if (nFieldIndex > 0)
                {
                    cmd.CommandText += " where ID_C = @ID_C" + ControlChars.CrLf;
                    Sql.AddParameter(cmd, "@ID_C", gID);
                    cmd.ExecuteNonQuery();
                }
            }
        }

        public Guid BeginTransaction()
        {
            Guid sessionId = Guid.NewGuid();

            Dictionary<Guid, OpenTransaction> transactions = null;

            if (HttpContext.Current.Application["WcfTransactions"] == null)
                HttpContext.Current.Application["WcfTransactions"] = new Dictionary<Guid, OpenTransaction>();

            transactions = HttpContext.Current.Application["WcfTransactions"] as Dictionary<Guid, OpenTransaction>;

            transactions.Add(sessionId, GetTransaction(Guid.Empty));

            return sessionId;
        }

        public void CommitTransaction(Guid transactionId)
        {
            if (HttpContext.Current.Application["WcfTransactions"] == null)
                throw new Exception("No transactions have been opened in this session");

            lock ((Dictionary<Guid, OpenTransaction>)HttpContext.Current.Application["WcfTransactions"])
            {
                CommitAndDispose(((Dictionary<Guid, OpenTransaction>)HttpContext.Current.Application["WcfTransactions"])[transactionId]);
                ((Dictionary<Guid, OpenTransaction>)HttpContext.Current.Application["WcfTransactions"]).Remove(transactionId);
            }
        }

        public void RollbackTransaction(Guid transactionId)
        {
            if (HttpContext.Current.Application["WcfTransactions"] == null)
                throw new Exception("No transactions have been opened in this session");

            lock ((Dictionary<Guid, OpenTransaction>)HttpContext.Current.Application["WcfTransactions"])
            {
               RollbackAndDispose(((Dictionary<Guid, OpenTransaction>)HttpContext.Current.Application["WcfTransactions"])[transactionId]);
               ((Dictionary<Guid, OpenTransaction>)HttpContext.Current.Application["WcfTransactions"]).Remove(transactionId);
            }
        }

        private void CommitAndDispose(OpenTransaction transaction)
        {
            if (transaction.Connection.State != ConnectionState.Closed)
            {
                transaction.Transaction.Commit();
                transaction.Connection.Close();
                transaction.Transaction.Dispose();
                transaction.Connection.Dispose();
            }
        }

        private void RollbackAndDispose(OpenTransaction transaction)
        {
            if (transaction.Connection.State != ConnectionState.Closed)
            {
                transaction.Transaction.Rollback();
                transaction.Connection.Close();
                transaction.Transaction.Dispose();
                transaction.Connection.Dispose();
            }
        }

        private OpenTransaction GetTransaction(Guid transactionId)
        {
            if (transactionId == Guid.Empty)
            {
                DbProviderFactory dbf = DbProviderFactories.GetFactory();
                IDbConnection con = dbf.CreateConnection();
                con.Open();
                IDbTransaction trn = con.BeginTransaction();
                return new OpenTransaction()
                    {
                        LastAccess = DateTime.Now,
                        Connection = con,
                        Transaction = trn
                    };
            }

            OpenTransaction trans = ((Dictionary<Guid, OpenTransaction>)HttpContext.Current.Application["WcfTransactions"])[transactionId];

            //Reset transaction
            if (trans.Connection.State == ConnectionState.Closed)
            {
                trans = GetTransaction(Guid.Empty);
                ((Dictionary<Guid, OpenTransaction>)HttpContext.Current.Application["WcfTransactions"])[transactionId] = trans;
            }

            return trans;
        }

        /// <summary>
        /// Syncs the tasks.
        /// </summary>
        /// <param name="lastSync">The last sync.</param>
        /// <param name="changedTasks">The changed tasks.</param>
        /// <returns></returns>
        public SyncResults SyncTasks(DateTime lastSync, List<Dictionary<string, string>> changedTasks)
        {
            List<Dictionary<string, string>> serverChangedTasks = null;

            if (lastSync != DateTime.MinValue)
            {
                serverChangedTasks = SearchFields(GetModuleInfo("Tasks"), 0, 100,
                    new SearchField() { Field = "ASSIGNED_USER_ID", Value = Security.USER_ID.ToString() },
                    new SearchField() { Operator = Operator.And },
                    new SearchField() { Field = "DATE_MODIFIED", Operator = Operator.GreaterThan, Value = lastSync.ToSqlDateTimeString() },
                    new SearchField() { Operator = Operator.And },
                    new SearchField() { Operator = Operator.OpenBracket },
                    new SearchField() { Field = "STATUS", Operator = Operator.NotEqual, Value = "Completed" },
                    new SearchField() { Operator = Operator.Or },
                    new SearchField() { Field = "STATUS", Operator = Operator.NotEqual, Value = "Deferred" },
                    new SearchField() { Operator = Operator.CloseBracker }).Results;
            }
            else
            {
                serverChangedTasks = SearchFields(GetModuleInfo("Tasks"), 0, 100,
                    new SearchField() { Field = "ASSIGNED_USER_ID", Value = Security.USER_ID.ToString() },
                    new SearchField() { Operator = Operator.And },
                    new SearchField() { Operator = Operator.OpenBracket },
                    new SearchField() { Field = "STATUS", Operator = Operator.NotEqual, Value = "Completed" },
                    new SearchField() { Operator = Operator.Or },
                    new SearchField() { Field = "STATUS", Operator = Operator.NotEqual, Value = "Deferred" },
                    new SearchField() { Operator = Operator.CloseBracker }).Results;
            }
            List<Dictionary<string, string>> conflicts = new List<Dictionary<string, string>>();
            List<Dictionary<string, string>> createdTasks = new List<Dictionary<string, string>>();

            //Things get complex when client is sending updated tasks to us
            if (changedTasks != null && changedTasks.Count > 0)
            {
                foreach (Dictionary<string, string> changedTask in changedTasks)
                {
                    //Make sure server task has not changed as well.
                    if (serverChangedTasks.Count(t => t["ID"] == changedTask["ID"]) == 0)
                    {
                        if ((changedTask.ContainsKey("ASSIGNED_USER_ID") && string.IsNullOrEmpty(changedTask["ASSIGNED_USER_ID"])) ||
                            !changedTask.ContainsKey("ASSIGNED_USER_ID"))
                            changedTask.Add("ASSIGNED_USER_ID", Security.USER_ID.ToString());

                        //Update server
                        UpdateResults results = UpdateEntry(Guid.Empty, GetModuleInfo("Tasks"),
                            changedTask);


                        if (changedTask["ID"] == string.Empty ||
                            changedTask["ID"] == Guid.Empty.ToString())
                        {
                            changedTask["ID"] = results.Id.ToString();
                            createdTasks.Add(changedTask);
                        }
                    }
                    else
                    {
                        conflicts.Add(changedTask);
                    }
                }
            }

            return new SyncResults()
                {
                    ChangedCrmItems = serverChangedTasks,
                    ConflictedItems = conflicts,
                    CreatedItems = createdTasks
                };
        }

        public SyncResults SyncProjectTasks(DateTime lastSync, List<Dictionary<string, string>> changedTasks)
        {
            List<Dictionary<string, string>> serverChangedTasks = null;

            if (lastSync != DateTime.MinValue)
            {
                serverChangedTasks = SearchFields(GetModuleInfo("ProjectTask"), "vwPROJECT_TASKS", 0, 100,
                    new SearchField() { Field = "ASSIGNED_USER_ID", Value = Security.USER_ID.ToString() },
                    new SearchField() { Operator = Operator.And },
                    new SearchField() { Field = "DATE_MODIFIED", Operator = Operator.GreaterThan, Value = lastSync.ToSqlDateTimeString() },
                    new SearchField() { Operator = Operator.And },
                    new SearchField() { Operator = Operator.OpenBracket },
                    new SearchField() { Field = "STATUS", Operator = Operator.NotEqual, Value = "Completed" },
                    new SearchField() { Operator = Operator.Or },
                    new SearchField() { Field = "STATUS", Operator = Operator.NotEqual, Value = "Deferred" },
                    new SearchField() { Operator = Operator.CloseBracker }).Results;
            }
            else
            {
                serverChangedTasks = SearchFields(GetModuleInfo("ProjectTask"), "vwPROJECT_TASKS", 0, 100,
                    new SearchField() { Field = "ASSIGNED_USER_ID", Value = Security.USER_ID.ToString() },
                    new SearchField() { Operator = Operator.And },
                    new SearchField() { Operator = Operator.OpenBracket },
                    new SearchField() { Field = "STATUS", Operator = Operator.NotEqual, Value = "Completed" },
                    new SearchField() { Operator = Operator.Or },
                    new SearchField() { Field = "STATUS", Operator = Operator.NotEqual, Value = "Deferred" },
                    new SearchField() { Operator = Operator.CloseBracker }).Results;
            }
            List<Dictionary<string, string>> conflicts = new List<Dictionary<string, string>>();
            List<Dictionary<string, string>> createdTasks = new List<Dictionary<string, string>>();

            //Things get complex when client is sending updated tasks to us
            if (changedTasks != null && changedTasks.Count > 0)
            {
                foreach (Dictionary<string, string> changedTask in changedTasks)
                {
                    //Make sure server task has not changed as well.
                    if (serverChangedTasks.Count(t => t["ID"] == changedTask["ID"]) == 0)
                    {
                        if ((changedTask.ContainsKey("ASSIGNED_USER_ID") && string.IsNullOrEmpty(changedTask["ASSIGNED_USER_ID"])) ||
                            !changedTask.ContainsKey("ASSIGNED_USER_ID"))
                            changedTask.Add("ASSIGNED_USER_ID", Security.USER_ID.ToString());

                        //Update server
                        UpdateResults results = UpdateEntry(Guid.Empty, GetModuleInfo("ProjectTask"),
                            changedTask);


                        if (changedTask["ID"] == string.Empty ||
                            changedTask["ID"] == Guid.Empty.ToString())
                        {
                            changedTask["ID"] = results.Id.ToString();
                            createdTasks.Add(changedTask);
                        }
                    }
                    else
                    {
                        conflicts.Add(changedTask);
                    }
                }
            }

            return new SyncResults()
            {
                ChangedCrmItems = serverChangedTasks,
                ConflictedItems = conflicts,
                CreatedItems = createdTasks
            };
        }

        public SyncResults SyncContacts(DateTime lastSync, List<Dictionary<string, string>> changedContacts)
        {
            List<Dictionary<string, string>> serverChangedContacts = null;

            StringBuilder from = new StringBuilder();

            from.AppendLine("vwCONTACTS_Edit");
            from.AppendLine("left outer join vwCONTACTS_USERS ");
            from.AppendLine("on vwCONTACTS_USERS.CONTACT_ID = vwCONTACTS_Edit.ID ");
            from.AppendLine("and vwCONTACTS_USERS.USER_ID = '" + Security.USER_ID + "'");

            if (lastSync != DateTime.MinValue)
            {
                serverChangedContacts = SearchFields(GetModuleInfo("Contacts"), from.ToString(), 0, 100,
                    new SearchField() { Field = "vwCONTACTS_USERS.CONTACT_ID", Operator = Operator.IsNotNull },
                    new SearchField() { Operator = Operator.And },
                    new SearchField() { Field = "DATE_MODIFIED", Operator = Operator.GreaterThan, Value = lastSync.ToSqlDateTimeString() }
                    ).Results;
            }
            else
            {
                serverChangedContacts = SearchFields(GetModuleInfo("Contacts"), from.ToString(), 0, 100,
                    new SearchField() { Field = "vwCONTACTS_USERS.CONTACT_ID", Operator = Operator.IsNotNull }
                    ).Results;
            }
            List<Dictionary<string, string>> conflicts = new List<Dictionary<string, string>>();
            List<Dictionary<string, string>> createdContacts = new List<Dictionary<string, string>>();

            //Things get complex when client is sending updated tasks to us
            if (changedContacts != null && changedContacts.Count > 0)
            {
                foreach (Dictionary<string, string> changedContact in changedContacts)
                {
                    //Make sure server task has not changed as well.
                    if (serverChangedContacts.Count(t => t["ID"] == changedContact["ID"]) == 0)
                    {
                        //Assign to the person making the web service call by default
                        if ((changedContact.ContainsKey("ASSIGNED_USER_ID") && string.IsNullOrEmpty(changedContact["ASSIGNED_USER_ID"])) ||
                            !changedContact.ContainsKey("ASSIGNED_USER_ID"))
                            changedContact.Add("ASSIGNED_USER_ID", Security.USER_ID.ToString());

                        //Update server
                        UpdateResults results = UpdateEntry(Guid.Empty, GetModuleInfo("Contacts"),
                            changedContact);


                        if (changedContact["ID"] == string.Empty ||
                            changedContact["ID"] == Guid.Empty.ToString())
                        {
                            changedContact["ID"] = results.Id.ToString();
                            createdContacts.Add(changedContact);
                        }
                    }
                    else
                    {
                        conflicts.Add(changedContact);
                    }
                }
            }

            return new SyncResults()
            {
                ChangedCrmItems = serverChangedContacts,
                ConflictedItems = conflicts,
                CreatedItems = createdContacts
            };
        }

        public SyncResults SyncModuleEntries(Module module, DateTime lastSync, List<Dictionary<string, string>> changedItems)
        {
            List<Dictionary<string, string>> serverChangedTasks = null;

            string fromView = null;

            List<SearchField> searchParams = new List<SearchField>();

            if (module.MODULE_NAME == "Contacts")
            {
                StringBuilder from = new StringBuilder();

                from.AppendLine("vwCONTACTS_Edit");
                from.AppendLine("inner join vwCONTACTS_USERS ");
                from.AppendLine("on vwCONTACTS_USERS.CONTACT_ID = vwCONTACTS_Edit.ID ");
                from.AppendLine("and vwCONTACTS_USERS.USER_ID = '" + Security.USER_ID + "'");

                fromView = from.ToString();
            }
            else
            {
                searchParams.Add(new SearchField() { Field = "ASSIGNED_USER_ID", Value = Security.USER_ID.ToString() });

                //Need the edit view, otherwise it will be missing the description field.
                fromView = _data.VwEDITVIEWS.SingleOrDefault(e => e.NAME == module.MODULE_NAME + ".EditView").VIEW_NAME;
            }

            if (lastSync != DateTime.MinValue)
            {
                if (searchParams.Count != 0)
                    searchParams.Add(new SearchField() { Operator = Operator.And });
                searchParams.Add(new SearchField() { Field = "DATE_MODIFIED", Operator = Operator.GreaterThan, Value = lastSync.ToSqlDateTimeString() });
            }

            serverChangedTasks = SearchFields(module, fromView, 0, 100, searchParams.ToArray()).Results;

            List<Dictionary<string, string>> conflicts = new List<Dictionary<string, string>>();
            List<Dictionary<string, string>> createdTasks = new List<Dictionary<string, string>>();
            List<Guid> deletedIds = new List<Guid>();

            //Things get complex when client is sending updated tasks to us
            if (changedItems != null && changedItems.Count > 0)
            {
                Guid transId = BeginTransaction();

                try
                {
                    foreach (Dictionary<string, string> changedItem in changedItems)
                    {
                        //Make sure server task has not changed as well.
                        if (serverChangedTasks.Count(t => t["ID"] == changedItem["ID"]) == 0)
                        {
                            UpdateResults results = UpdateEntry(transId, module, changedItem);

                            if (changedItem["ID"] == string.Empty ||
                                changedItem["ID"] == Guid.Empty.ToString())
                            {
                                //Only set the assigned to if it is a new item, otherwise we will overwrite new assigned to
                                //Also do not want to assign a contact to anyone by default
                                if (((changedItem.ContainsKey("ASSIGNED_USER_ID") && string.IsNullOrEmpty(changedItem["ASSIGNED_USER_ID"])) ||
                                    !changedItem.ContainsKey("ASSIGNED_USER_ID")) &&
                                    module.MODULE_NAME != "Contacts")
                                    changedItem.Add("ASSIGNED_USER_ID", Security.USER_ID.ToString());

                                changedItem["ID"] = results.Id.ToString();
                                createdTasks.Add(changedItem);
                            }
                            else
                            {
                                // We need to delete from client if item has been reassigned (and it isnt a contact
                                if (changedItem.ContainsKey("ASSIGNED_USER_ID")
                                    && changedItem["ASSIGNED_USER_ID"] != Security.USER_ID.ToString() &&
                                    module.MODULE_NAME != "Contacts")
                                    deletedIds.Add(results.Id);
                            }
                        }
                        else
                        {
                            conflicts.Add(changedItem);
                        }
                    }

                    CommitTransaction(transId);
                }
                catch (Exception)
                {
                    RollbackTransaction(transId);
                    throw;
                }
            }

            //Handle deleted items
            SearchResults deletedItemsResults = null;

            if (lastSync != DateTime.MinValue)
            {
                deletedItemsResults = SearchFields(module, VerifyModuleName(module.MODULE_NAME), 0, 100,
                 new SearchField() { Field = "DELETED", Value = "1", Operator = Operator.Equal },
                 new SearchField() { Operator = Operator.And },
                 new SearchField() { Field = "DATE_MODIFIED", Operator = Operator.GreaterThan, Value = lastSync.ToSqlDateTimeString() });
            }
            else
            {
                deletedItemsResults = SearchFields(module, VerifyModuleName(module.MODULE_NAME), 0, 100,
                 new SearchField() { Field = "DELETED", Value = "1", Operator = Operator.Equal });
            }
            
            foreach (string id in deletedItemsResults.Results.Select(r => r["ID"]))
                deletedIds.Add(new Guid(id));

            return new SyncResults()
            {
                ChangedCrmItems = serverChangedTasks,
                ConflictedItems = conflicts,
                CreatedItems = createdTasks,
                DeletedItems = deletedIds
            };
        }
    }

    public class OpenTransaction
    {
        private DateTime _lastAccess;

        public DateTime LastAccess
        {
            get { return _lastAccess; }
            set { _lastAccess = value; }
        }
        private IDbTransaction _transaction;

        public IDbTransaction Transaction
        {
            get { return _transaction; }
            set { _transaction = value; }
        }
        private IDbConnection _connection;

        public IDbConnection Connection
        {
            get { return _connection; }
            set { _connection = value; }
        }
    }

    static class Extensions
    {
        public static string ToSqlDateTimeString(this DateTime dt)
        {
            return dt.ToString("yyyy-MM-dd HH:mm:ss");
        }
    }
}
